﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;

namespace L2Sql.FrameWork
{
    public class DbHelper
    {
        /*=========MS Sql Server 开始===============*/

        #region MS Sql Server IDataReader

        /// <summary>
        /// 返回 IDataReader
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <param name="ConnName">连接字符串名称</param>
        /// <returns>返回IDataReader</returns>
        public IDataReader ExecuteReader(string SqlStr, string ConnName = "ConnectionString")
        {
            SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnName].ToString());
            SqlCommand Comm = new SqlCommand(SqlStr, Conn);
            Conn.Open();
            return (IDataReader)Comm.ExecuteReader(CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 返回IDataReader
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <param name="Params">object参数数组</param>
        /// <param name="ConnName">数据库连接字符串</param>
        /// <returns>返回IDataReader</returns>
        public IDataReader ExecuteReader(string SqlStr, object Params, string ConnName = "ConnectionString")
        {
            SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnName].ToString());
            Conn.Open();
            SqlCommand Comm = new SqlCommand(SqlStr, Conn);
            if (Params != null)
            {
                Comm.Parameters.Add(Params);
            }
            IDataReader sdr = Comm.ExecuteReader(CommandBehavior.CloseConnection);
            Comm.Parameters.Clear();
            return (IDataReader)sdr;
        }
        /// <summary>
        /// 返回 IDataReader
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <param name="Params">object参数数组</param>
        /// <param name="ConnName">数据库连接字符串</param>
        /// <returns>返回值 IDataReader类型的参数</returns>
        public IDataReader ExecuteReader(string SqlStr, object[] Params, string ConnName = "ConnectionString")
        {
            SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnName].ToString());
            Conn.Open();
            SqlCommand Comm = new SqlCommand(SqlStr, Conn);
            if (Params != null)
            {
                foreach (object P in Params)
                {
                    Comm.Parameters.Add(P);
                }
            }
            IDataReader sdr = Comm.ExecuteReader(CommandBehavior.CloseConnection);
            Comm.Parameters.Clear();
            return (IDataReader)sdr;
        }

        #endregion IDataReader

        #region MS Sql Server DataTable

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <param name="ConnName">数据库连接字符串</param>
        /// <returns></returns>
        public DataTable ExecuteTable(string SqlStr, string ConnName = "ConnectionString")
        {
            SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnName].ToString());
            SqlDataAdapter Da = new SqlDataAdapter(SqlStr, Conn);
            try
            {
                DataTable Dt = new DataTable();
                Da.Fill(Dt);
                return Dt;
            }
            catch
            {
                return null;
            }
            finally
            {
                Da.Dispose();
            }
        }

        /// <summary>
        /// 返回 DataTable
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <param name="Params">object参数数组</param>
        /// <param name="ConnName">数据库连接字符串</param>
        /// <returns>返回值 DataTable类型的参数</returns>
        public DataTable ExecuteTable(string SqlStr, object Params, string ConnName = "ConnectionString")
        {
            SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnName].ToString());
            SqlCommand Comm = new SqlCommand(SqlStr, Conn);
            if (Params != null)
            {
                Comm.Parameters.Add(Params);
            }
            SqlDataAdapter Da = new SqlDataAdapter(Comm);
            try
            {
                DataTable Dt = new DataTable();
                Da.Fill(Dt);
                return Dt;
            }
            catch
            {
                return null;
            }
            finally
            {
                Comm.Parameters.Clear();
                Da.Dispose();
                Conn.Close();
            }
        }

        /// <summary>
        /// 返回 DataTable
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <param name="Params">object参数数组</param>
        /// <param name="ConnName">数据库连接字符串</param>
        /// <returns>返回值 DataTable类型的参数</returns>
        public DataTable ExecuteTable(string SqlStr, object[] Params, string ConnName = "ConnectionString")
        {
            SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnName].ToString());
            SqlCommand Comm = new SqlCommand(SqlStr, Conn);
            if (Params != null)
            {
                foreach (object P in Params)
                {
                    Comm.Parameters.Add(P);
                }
            }
            SqlDataAdapter Da = new SqlDataAdapter(Comm);
            try
            {
                DataTable Dt = new DataTable();
                Da.Fill(Dt);
                return Dt;
            }
            catch
            {
                return null;
            }
            finally
            {
                Comm.Parameters.Clear();
                Da.Dispose();
                Conn.Close();
            }
        }

        /// <summary>
        /// 分页存储过程 返回DataTable
        /// </summary>
        /// <param name="total">输出参数 总数量</param>
        /// <param name="tbName">表名(多表逗号隔开)</param>
        /// <param name="Fields">字段列表</param>
        /// <param name="OrderBy">排序</param>
        /// <param name="PageSize">每页数量</param>
        /// <param name="PageIndex">页码</param>
        /// <param name="Where">where条件</param>
        /// <param name="Params">参数</param>
        /// <param name="ConnName">数据库连接字符串</param>
        /// <returns></returns>
        public DataTable ExecuteTablePage(ref int total, string tbName, string Fields, string OrderBy, int PageSize, int PageIndex, string Where, object[] Params, string ConnName = "")
        {
            ConnName = ConnName == "" ? "ConnectionString" : ConnName;

            SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnName].ToString());
            SqlCommand Comm = new SqlCommand("page", Conn);
            Comm.CommandType = CommandType.StoredProcedure;

            Comm.Parameters.Add(new SqlParameter("@tbName", tbName));
            if (Fields != "")
            {
                Comm.Parameters.Add(new SqlParameter("@Fields", Fields));
            }
            if (OrderBy != "")
            {
                Comm.Parameters.Add(new SqlParameter("@OrderBy", OrderBy));
            }
            if (Where != "")
            {
                Comm.Parameters.Add(new SqlParameter("@Where", Where));
            }
            if (PageSize > 0)
            {
                Comm.Parameters.Add(new SqlParameter("@PageSize", PageSize));
            }
            if (PageIndex > 0)
            {
                Comm.Parameters.Add(new SqlParameter("@PageIndex", PageIndex));
            }

            SqlParameter para = new SqlParameter("@total", SqlDbType.Int, 20);//定义output参数类型
            para.Direction = ParameterDirection.Output;//设置这个参数的类型是输出参数
            Comm.Parameters.Add(para);

            if (Params != null)
            {
                foreach (object P in Params)
                {
                    Comm.Parameters.Add(P);
                }
            }

            SqlDataAdapter Da = new SqlDataAdapter(Comm);
            try
            {
                DataTable Dt = new DataTable();
                Da.Fill(Dt);
                string t = Comm.Parameters["@total"].Value.ToString();
                total = Convert.ToInt32(Comm.Parameters["@total"].Value.ToString());
                return Dt;
            }
            catch
            {
                return null;
            }
            finally
            {
                Comm.Parameters.Clear();
                Da.Dispose();
                Conn.Close();
            }
        }
        #endregion

        #region MS Sql Server ExecuteScalar

        /// <summary>
        /// 返回sql语句的查询结果
        /// </summary>
        /// <param name="SqlStr">SQL语句</param>
        /// <param name="ConnName">数据库连接名称</param>
        /// <returns>返回的是一个字符串</returns>
        public string ExecuteScalar(string SqlStr, string ConnName = "ConnectionString")
        {
            try
            {
                string i = "";
                IDataReader sdr = ExecuteReader(SqlStr, ConnName);
                if (sdr.Read())
                {
                    i = sdr[0].ToString();
                }
                sdr.Close();
                return i;
            }
            catch
            {
                HttpContext.Current.Response.Write(SqlStr);
                HttpContext.Current.Response.End();
            }
            return "";
        }

        /// <summary>
        /// 返回 object
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <param name="Params">object参数数组</param>
        /// <param name="ConnName">数据库连接名称</param>
        /// <returns>返回值 int类型的参数</returns>
        public string ExecuteScalar(string SqlStr, object Params, string ConnName = "ConnectionString")
        {
            try
            {
                string i = "";
                IDataReader sdr = ExecuteReader(SqlStr, Params, ConnName);
                if (sdr.Read())
                {
                    i = sdr[0].ToString();
                }
                sdr.Close();
                return i;
            }
            catch
            {
                HttpContext.Current.Response.Write(SqlStr);
                HttpContext.Current.Response.End();
            }
            return null;
        }

        /// <summary>
        /// 返回 object
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <param name="Params">object参数数组</param>
        /// <param name="ConnName">数据库连接名称</param>
        /// <returns>返回值 int类型的参数</returns>
        public string ExecuteScalar(string SqlStr, object[] Params, string ConnName = "ConnectionString")
        {
            try
            {
                string i = "";
                IDataReader sdr = ExecuteReader(SqlStr, Params, ConnName);
                if (sdr.Read())
                {
                    i = sdr[0].ToString();
                }
                sdr.Close();
                return i;
            }
            catch
            {
                HttpContext.Current.Response.Write(SqlStr);
                HttpContext.Current.Response.End();
            }
            return null;
        }
        #endregion ExecuteScalar

        #region MS Sql Server ExecuteScalars

        /// <summary>
        /// 返回查询的第一个字段值
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <param name="ConnName">数据库连接名称</param>
        /// <returns></returns>
        public string ExecuteScalars(string SqlStr, string ConnName = "ConnectionString")
        {
            try
            {
                StringBuilder str = new StringBuilder(); string ls = "";
                IDataReader sdr = ExecuteReader(SqlStr, ConnName);
                while (sdr.Read())
                {
                    str.Append(ls + sdr[0].ToString());
                    ls = ",";
                }
                sdr.Close();
                return str.ToString();
            }
            catch
            {
                HttpContext.Current.Response.Write(SqlStr + "<br />");
            }
            return null;
        }

        /// <summary>
        /// 返回查询的第一个字段值,逗号隔开
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <param name="Params">参数列表</param>
        /// <param name="ConnName">数据库连接名称</param>
        /// <returns></returns>
        public string ExecuteScalars(string SqlStr, object Params, string ConnName = "ConnectionString")
        {
            StringBuilder str = new StringBuilder(); string ls = "";
            IDataReader sdr = ExecuteReader(SqlStr, Params, ConnName);
            while (sdr.Read())
            {
                str.Append(ls + sdr[0].ToString());
                ls = ",";
            }
            sdr.Close();
            return str.ToString();
        }
        /// <summary>
        /// 返回查询的第一个字段值,逗号隔开
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <param name="Params">参数列表</param>
        /// <param name="ConnName">数据库连接名称</param>
        /// <returns></returns>
        public string ExecuteScalars(string SqlStr, object[] Params, string ConnName = "ConnectionString")
        {
            StringBuilder str = new StringBuilder(); string ls = "";
            IDataReader sdr = ExecuteReader(SqlStr, Params, ConnName);
            while (sdr.Read())
            {
                str.Append(ls + sdr[0].ToString());
                ls = ",";
            }
            sdr.Close();
            return str.ToString();
        }

        #endregion ExecuteScalars

        #region MS Sql Server ExecuteNonQuery

        /// <summary>
        /// 执行ExecuteNonQuery
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <param name="ConnName">数据库连接名称</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string SqlStr, string ConnName = "ConnectionString")
        {
            SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnName].ToString());
            Conn.Open();
            SqlCommand Comm = new SqlCommand(SqlStr, Conn);
            try
            {
                return Comm.ExecuteNonQuery();
            }
            catch
            {
                return 0;
            }
            finally
            {
                Conn.Close();
            }
        }

        /// <summary>
        /// 执行ExecuteNonQuery
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <param name="Params">object参数数组</param>
        /// <param name="ConnName">数据库连接名称</param>
        /// <returns>返回值 int类型的参数</returns>
        public int ExecuteNonQuery(string SqlStr, object Params, string ConnName = "ConnectionString")
        {
            SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnName].ToString());
            Conn.Open();
            SqlCommand Comm = new SqlCommand(SqlStr, Conn);
            if (Params != null)
            {
                Comm.Parameters.Add(Params);
            }
            SqlDataAdapter Da = new SqlDataAdapter(Comm);
            try
            {
                return Comm.ExecuteNonQuery();
            }
            catch
            {
                return 0;
            }
            finally
            {
                Comm.Parameters.Clear();
                Da.Dispose();
                Conn.Close();
            }
        }

        /// <summary>
        /// 执行 ExecuteNonQuery
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <param name="Params">object参数数组</param>
        /// <param name="ConnName">数据库连接名称</param>
        /// <returns>返回值 int类型的参数</returns>
        public int ExecuteNonQuery(string SqlStr, object[] Params, string ConnName = "ConnectionString")
        {

            SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnName].ToString());
            Conn.Open();
            SqlCommand Comm = new SqlCommand(SqlStr, Conn);
            if (Params != null)
            {
                foreach (var P in Params)
                {
                    Comm.Parameters.Add(P);
                }
            }
            SqlDataAdapter Da = new SqlDataAdapter(Comm);
            try
            {
                return Comm.ExecuteNonQuery();
            }
            catch
            {
                return 0;
            }
            finally
            {
                Comm.Parameters.Clear();
                Da.Dispose();
                Conn.Close();
            }
        }
        #endregion ExecuteNonQuery  

        #region MS Sql Server 添加SqlParameter参数
        /// <summary>
        /// 添加 mssql 参数话查询 参数
        /// </summary>
        /// <param name="ParamName">参数名 @名称 </param>
        /// <param name="sqlType">参数类型</param>
        /// <param name="Size">参数的长度</param>
        /// <param name="value">参数的值</param>
        /// <returns>返回 SqlParameter 对象</returns>
        public SqlParameter AddParam(string ParamName, SqlDbType sqlType, int Size, object value)
        {
            SqlParameter param = new SqlParameter(ParamName, sqlType, Size);
            param.Value = value;
            return param;
        }

        /// <summary>
        /// 添加 mssql 参数话查询 参数
        /// </summary>
        /// <param name="ParamName">参数名 @名称</param>
        /// <param name="value">参数的值</param>
        /// <returns>返回 SqlParameter 对象</returns>
        public SqlParameter AddParam(string ParamName, object value)
        {
            return new SqlParameter(ParamName, value);
        }

        #endregion MS Sql Server 添加SqlParameter参数

        /*=========MS Sql Server 结束===============*/
    }
}
